package com.mairuide.transactionstreamcount.service;

import java.math.BigDecimal;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.UUID;
import java.util.stream.Collectors;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

/**
 * Created by 敲代码的卡卡罗特
 * on 2018/12/4 11:25.
 */

import com.mairuide._frame.base.BaseRepository;
import com.mairuide._frame.base.BaseServiceProxy;
import com.mairuide._frame.utils.Testtrans;
import com.mairuide.agency.service.AgencyService;
import com.mairuide.bill.DealFlow;
import com.mairuide.bill.DealFlowService;
import com.mairuide.bill.DealFlowVo;
import com.mairuide.bill.utils.CollectionsUtils;
import com.mairuide.bill.utils.DateUtils;
import com.mairuide.casemanage.service.CaseManageService;
import com.mairuide.entity.PageResult;
import com.mairuide.transactionstreamcount.Canvas;
import com.mairuide.transactionstreamcount.DealFlowQueryDto;
import com.mairuide.transactionstreamcount.DealflowVO;
import com.mairuide.transactionstreamcount.MonthAnalysisDto;

@Service
@Transactional(readOnly = true)
public class TransactionStreamCountService extends BaseServiceProxy<DealFlow> {

	@Autowired
	private JdbcTemplate jdbcTemplate;
	@PersistenceContext
	private EntityManager entityManager;
	@Autowired
	private AgencyService agencyService;
	@Autowired
	private DealFlowService dealFlowService;
	@Autowired
	private CaseManageService caseManageService;

	@Override
	protected EntityManager getEntityManager() {
		return entityManager;
	}

	/**
	 *
	 * @author 刘中华
	 * @date 2018/12/4 11:30
	 * @param [casemanageId,
	 *            account_body_card]
	 * @return java.util.Map<java.lang.String,java.lang.Object>
	 * @Description 流入 账号统计
	 */
	public PageResult getInStreamAccountCount(String minMoney, String maxMoney, String casemanageId,
			String account_body_card, String rivalName, Integer page, Integer size, String beginTime, String endTime,
			String sortName, String sortOrder) {
		String whichTable = caseManageService.getWhichTable(casemanageId);
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT df.*, ");
		sb.append(" ROUND( df.inMoney / df.totalMoney * 100, 2 )  AS inMoneyPer,");
//		sb.append(" ROUND( df.outMoney / df.totalMoney * 100, 2 )  AS outMoneyPer,");
		sb.append(" df.inMoney - df.outMoney AS PureInMoney,");
		sb.append(" df.outMoney - df.inMoney AS PureOutMoney,");
//		sb.append(" ROUND( df.outNumber / df.totalNumber * 100, 2 )  AS outNumberPer,");
		sb.append(" ROUND( df.inNumber / df.totalNumber * 100, 2 )  AS inNumberPer ");
		sb.append(" FROM (SELECT ACCOUNT_RIVAL as ACCOUNT_RIVAL,account_rival_card,SUM( abs(account_money) ) AS totalMoney,COUNT( 1 ) AS totalNumber,");
		sb.append(" COALESCE( sum( CASE WHEN sign = '0' THEN abs(account_money) END ), 0 ) AS inMoney,");
		sb.append(" COALESCE( sum( CASE WHEN sign = '1' THEN abs(account_money) END ), 0 ) AS outMoney,");
		sb.append(" COUNT( CASE WHEN sign = '0' THEN 1 END ) AS inNumber,");
		sb.append(" COUNT( CASE WHEN sign = '1' THEN 1 END ) AS outNumber ");
		sb.append(" FROM "+whichTable+" WHERE  account_rival_card IS NOT NULL AND  casemanage_id = '" + casemanageId
				+ "' ");
		if (StringUtils.isNotBlank(account_body_card)) {
			if (account_body_card.split(",").length < 1000) {
				sb.append(" AND account_body_card in ( " + account_body_card + " )");
				sb.append(" AND account_rival_card not in(" + account_body_card + ")");
			}else {
				sb.append(" AND account_rival_card !=ACCOUNT_BODY_CARD ");
			}
		} else {
			sb.append(" AND 1=0");
		}
		if (StringUtils.isNotBlank(rivalName)) {
			sb.append(" AND account_rival like '%" + rivalName + "%'");
		}
		if (StringUtils.isNotBlank(minMoney)) {
			sb.append(" AND account_money >=" + minMoney);
		}
		if (StringUtils.isNotBlank(maxMoney)) {
			sb.append(" AND account_money <=" + maxMoney);
		}
		if (StringUtils.isNotBlank(beginTime)) {
			sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') >= '" + beginTime + "'");
		}
		if (StringUtils.isNotBlank(endTime)) {
			sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') <= '" + endTime + "'");
		}
		sb.append(" GROUP BY account_rival_card,ACCOUNT_RIVAL ) df where  df.TOTALMONEY>0  AND df.TOTALNUMBER>0 and df.inNumber>0 ");
		sb.append(" ORDER BY INMONEY DESC ");
		Query query = this.entityManager.createNativeQuery(sb.toString());
		// 将查询结果集转为Map
		query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		// 设置分页
		query.setFirstResult(0);
		query.setMaxResults(20);
		// 获取查询结果集
		List<Map<String, Object>> list = query.getResultList();
		PageResult pageResult = new PageResult(null, null, list);
		return pageResult;
	}
    /**
     *
     * @author 刘中华
     * @date 2018/12/4 11:30
     * @param [casemanageId,
     *            account_body_card]
     * @return java.util.Map<java.lang.String,java.lang.Object>
     * @Description 流入 账号统计
     */
    public PageResult getInStreamAccountCount_easyui(DealFlowQueryDto df) {
        String whichTable = caseManageService.getWhichTable(df.getCaseId());
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT df.*, ");
        sb.append(" ROUND( df.inMoney / df.totalMoney * 100, 2 )  AS inMoneyPer,");
        sb.append(" df.inMoney - df.outMoney AS PureInMoney,");
        sb.append(" df.outMoney - df.inMoney AS PureOutMoney,");
        sb.append(" ROUND( df.inNumber / df.totalNumber * 100, 2 )  AS inNumberPer ");
        sb.append(" FROM (SELECT ACCOUNT_RIVAL as ACCOUNT_RIVAL,account_rival_card,SUM( abs(account_money) ) AS totalMoney,COUNT( 1 ) AS totalNumber,");
        sb.append(" COALESCE( sum( CASE WHEN sign = '0' THEN abs(account_money) END ), 0 ) AS inMoney,");
        sb.append(" COALESCE( sum( CASE WHEN sign = '1' THEN abs(account_money) END ), 0 ) AS outMoney,");
        sb.append(" COUNT( CASE WHEN sign = '0' THEN 1 END ) AS inNumber,");
        sb.append(" COUNT( CASE WHEN sign = '1' THEN 1 END ) AS outNumber ");
        sb.append(" FROM "+whichTable+" WHERE  account_rival_card IS NOT NULL AND  casemanage_id = '" + df.getCaseId()
                + "' ");
        if (StringUtils.isNotBlank(df.getCardList())) {
            if (df.getCardList().split(",").length < 1000) {
                sb.append(" AND account_body_card in ( " + df.getCardList() + " )");
                sb.append(" AND account_rival_card not in(" + df.getCardList() + ")");
            }else {
                sb.append(" AND account_rival_card !=ACCOUNT_BODY_CARD ");
            }
        } else {
            sb.append(" AND 1=0");
        }
        if (StringUtils.isNotBlank(df.getRivalName())) {
            sb.append(" AND account_rival like '%" + df.getRivalName() + "%'");
        }
        if (StringUtils.isNotBlank(df.getMinMoney())) {
            sb.append(" AND account_money >=" + df.getMinMoney());
        }
        if (StringUtils.isNotBlank(df.getMaxMoney())) {
            sb.append(" AND account_money <=" + df.getMaxMoney());
        }
        if (StringUtils.isNotBlank(df.getBeginTime())) {
            sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') >= '" + df.getBeginTime() + "'");
        }
        if (StringUtils.isNotBlank(df.getEndTime())) {
            sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') <= '" + df.getEndTime() + "'");
        }
        sb.append(
                " GROUP BY account_rival_card,account_rival ) df where  df.TOTALMONEY>0  AND df.TOTALNUMBER>0 and df.inNumber>0 ");
        if (StringUtils.isNotBlank(df.getSort())) {
            sb.append(" order by " + df.getSort() + " " + df.getOrder());
        }else {
        	sb.append(" order by PUREINMONEY desc ");
        }
        Query query = this.entityManager.createNativeQuery(sb.toString());
		// 获取总结果集
		List<Map<String, Object>> maps = query.getResultList();
        // 将查询结果集转为Map
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        // 设置分页
        query.setFirstResult((df.getPage() - 1) * df.getRows());
        query.setMaxResults(df.getRows());
        // 获取查询结果集
        List<Map<String, Object>> list = query.getResultList();
        PageResult pageResult = new PageResult(Long.valueOf(maps.size()), list);
        return pageResult;
    }

	/**
	 *
	 * @author 刘中华
	 * @date 2018/12/4 11:30
	 * @param [casemanageId,
	 *            account_body_card]
	 * @return java.util.Map<java.lang.String,java.lang.Object>
	 * @Description 流出 账号统计
	 */
	public PageResult getOutStreamAccountCount(String minMoney, String maxMoney, String casemanageId,
			String account_body_card, String rivalName, Integer page, Integer size, String beginTime, String endTime,
			String sortName, String sortOrder) {
		String whichTable = caseManageService.getWhichTable(casemanageId);
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT df.*, ");
		sb.append(" ROUND( df.outMoney / df.totalMoney * 100, 2 )  AS outMoneyPer,");
		sb.append(" df.inMoney - df.outMoney AS PureInMoney,");
		sb.append(" df.outMoney - df.inMoney AS PureOutMoney,");
		sb.append(" ROUND( df.outNumber / df.totalNumber * 100, 2 )  AS outNumberPer ");
		sb.append(" FROM (SELECT ACCOUNT_RIVAL as ACCOUNT_RIVAL,account_rival_card,SUM( abs(account_money) ) AS totalMoney,COUNT( 1 ) AS totalNumber,");
		sb.append(" COALESCE( sum( CASE WHEN sign = '0' THEN abs(account_money) END ), 0 ) AS inMoney,");
		sb.append(" COALESCE( sum( CASE WHEN sign = '1' THEN abs(account_money) END ), 0 ) AS outMoney,");
		sb.append(" COUNT( CASE WHEN sign = '0' THEN 1 END ) AS inNumber,");
		sb.append(" COUNT( CASE WHEN sign = '1' THEN 1 END ) AS outNumber ");
		sb.append(
				" FROM "+whichTable+" WHERE  account_rival_card IS NOT NULL AND casemanage_id = '" + casemanageId + "'");
		if (StringUtils.isNotBlank(account_body_card)) {
			if (account_body_card.split(",").length < 1000) {
				sb.append(" AND account_body_card in ( " + account_body_card + " )");
				sb.append(" AND account_rival_card not in(" + account_body_card + ")");
			}else {
				sb.append(" AND account_rival_card !=ACCOUNT_BODY_CARD ");
			}
		} else {
			sb.append(" AND 1=0");
		}
		if (StringUtils.isNotBlank(rivalName)) {
			sb.append(" AND account_rival like '%" + rivalName + "%'");
		}
		if (StringUtils.isNotBlank(minMoney)) {
			sb.append(" AND account_money >=" + minMoney);
		}
		if (StringUtils.isNotBlank(maxMoney)) {
			sb.append(" AND account_money <=" + maxMoney);
		}
		if (StringUtils.isNotBlank(beginTime)) {
			sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') >= '" + beginTime + "'");
		}
		if (StringUtils.isNotBlank(endTime)) {
			sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') <= '" + endTime + "'");
		}
		sb.append(" GROUP BY account_rival_card,ACCOUNT_RIVAL ) df where  df.TOTALMONEY>0  AND df.TOTALNUMBER>0 and df.outNumber>0 ");
		sb.append(" ORDER BY OUTMONEY DESC ");
		Query query = this.entityManager.createNativeQuery(sb.toString());
		// 将查询结果集转为Map
		query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		// 设置分页
		query.setFirstResult(0);
		query.setMaxResults(20);
		// 获取查询结果集
		List<Map<String, Object>> list = query.getResultList();
		PageResult pageResult = new PageResult(null, null, list);
		return pageResult;
	}

	/**
	 *
	 * @author 刘中华
	 * @date 2018/12/4 11:30
	 * @param [casemanageId,
	 *            account_body_card]
	 * @return java.util.Map<java.lang.String,java.lang.Object>
	 * @Description 流出 账号统计
	 */
	public PageResult getOutStreamAccountCount_easyui(DealFlowQueryDto df) {
		String whichTable = caseManageService.getWhichTable(df.getCaseId());
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT df.*, ");
//		sb.append(" ROUND( df.inMoney / df.totalMoney * 100, 2 )  AS inMoneyPer,");
		sb.append(" ROUND( df.outMoney / df.totalMoney * 100, 2 )  AS outMoneyPer,");
		sb.append(" df.inMoney - df.outMoney AS PureInMoney,");
		sb.append(" df.outMoney - df.inMoney AS PureOutMoney,");
		sb.append(" ROUND( df.outNumber / df.totalNumber * 100, 2 )  AS outNumberPer ");
//		sb.append(" ROUND( df.inNumber / df.totalNumber * 100, 2 )  AS inNumberPer ");
		sb.append(" FROM (SELECT ACCOUNT_RIVAL as ACCOUNT_RIVAL,account_rival_card,SUM( abs(account_money) ) AS totalMoney,COUNT( 1 ) AS totalNumber,");
		sb.append(" COALESCE( sum( CASE WHEN sign = '0' THEN abs(account_money) END ), 0 ) AS inMoney,");
		sb.append(" COALESCE( sum( CASE WHEN sign = '1' THEN abs(account_money) END ), 0 ) AS outMoney,");
		sb.append(" COUNT( CASE WHEN sign = '0' THEN 1 END ) AS inNumber,");
		sb.append(" COUNT( CASE WHEN sign = '1' THEN 1 END ) AS outNumber ");
		sb.append(
				" FROM "+whichTable+" WHERE  account_rival_card IS NOT NULL AND casemanage_id = '" + df.getCaseId() + "'");
		if (StringUtils.isNotBlank(df.getCardList())) {
			if (df.getCardList().split(",").length < 1000) {
				sb.append(" AND account_body_card in ( " + df.getCardList() + " )");
				sb.append(" AND account_rival_card not in(" + df.getCardList() + ")");
			}else {
				sb.append(" AND account_rival_card !=ACCOUNT_BODY_CARD ");
			}
		} else {
			sb.append(" AND 1=0");
		}
		if (StringUtils.isNotBlank(df.getRivalName())) {
			sb.append(" AND account_rival like '%" + df.getRivalName() + "%'");
		}
		if (StringUtils.isNotBlank(df.getMinMoney())) {
			sb.append(" AND account_money >=" + df.getMinMoney());
		}
		if (StringUtils.isNotBlank(df.getMaxMoney())) {
			sb.append(" AND account_money <=" + df.getMaxMoney());
		}
		if (StringUtils.isNotBlank(df.getBeginTime())) {
			sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') >= '" + df.getBeginTime() + "'");
		}
		if (StringUtils.isNotBlank(df.getEndTime())) {
			sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') <= '" + df.getEndTime() + "'");
		}
		sb.append(
				" GROUP BY account_rival_card,ACCOUNT_RIVAL ) df where  df.TOTALMONEY>0  AND df.TOTALNUMBER>0 and df.outNumber>0 ");
		if (StringUtils.isNotBlank(df.getSort())) {
			sb.append(" order by " + df.getSort() + " " + df.getOrder());
		}else {
			sb.append(" order by PUREOUTMONEY desc ");
		}
		Query query = this.entityManager.createNativeQuery(sb.toString());
		// 获取总结果集
		List<Map<String, Object>> maps = query.getResultList();
		// 将查询结果集转为Map
		query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		// 设置分页
		query.setFirstResult((df.getPage() - 1) * df.getRows());
		query.setMaxResults(df.getRows());
		// 获取查询结果集
		List<Map<String, Object>> list = query.getResultList();
		PageResult pageResult = new PageResult(Long.valueOf(maps.size()), list);
		return pageResult;
	}

	/**
	 *
	 * @author 刘中华
	 * @date 2018/12/4 11:30
	 * @param [casemanageId,
	 *            account_body_card]
	 * @return java.util.Map<java.lang.String,java.lang.Object>
	 * @Description 流入/流出 主体统计
	 */
	public PageResult getInStreamBodyCount(String minMoney, String maxMoney, String casemanageId,
			String account_body_card, String rivalName, Integer page, Integer size, String beginTime, String endTime,
			String sortName, String sortOrder) {
		String whichTable = caseManageService.getWhichTable(casemanageId);
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT df.*, ");
		sb.append(" concat( ROUND( df.inMoney / df.totalMoney * 100, 2 ), '%' )  AS inMoneyPer,");
		sb.append(" concat( ROUND( df.outMoney / df.totalMoney * 100, 2 ), '%' )  AS outMoneyPer,");
		sb.append(" df.inMoney - df.outMoney AS PureInMoney,");
		sb.append(" df.outMoney - df.inMoney AS PureOutMoney,");
		sb.append(" concat( ROUND( df.outNumber / df.totalNumber * 100, 2 ), '%' )  AS outNumberPer,");
		sb.append(" concat( ROUND( df.inNumber / df.totalNumber * 100, 2 ), '%' )  AS inNumberPer FROM (");
		sb.append(
				" SELECT MAX(ACCOUNT_BODY) as ACCOUNT_BODY,ACCOUNT_BODY_CARD,account_rival,SUM( abs(account_money) ) AS totalMoney,COUNT( 1 ) AS totalNumber,");
		sb.append(" COALESCE( sum( CASE WHEN sign = '0' THEN abs(account_money) END ), 0 ) AS inMoney,");
		sb.append(" COALESCE( sum( CASE WHEN sign = '1' THEN abs(account_money) END ), 0 ) AS outMoney,");
		sb.append(" COUNT( CASE WHEN sign = '0' THEN 1 END ) AS inNumber,");
		sb.append(" COUNT( CASE WHEN sign = '1' THEN 1 END ) AS outNumber ");
		sb.append(" FROM "+whichTable+" WHERE   account_rival_card IS NOT NULL AND casemanage_id = '" + casemanageId
				+ "' AND account_rival is not NULL ");
		if (StringUtils.isNotBlank(account_body_card)) {
			if (account_body_card.split(",").length < 1000)
				sb.append(" AND account_body_card in ( " + account_body_card + " )");
		} else {
			sb.append(" AND 1=0");
		}
		if (StringUtils.isNotBlank(rivalName)) {
			sb.append(" AND account_rival like '%" + rivalName + "%'");
		}
		if (StringUtils.isNotBlank(minMoney)) {
			sb.append(" AND account_money >=" + minMoney);
		}
		if (StringUtils.isNotBlank(maxMoney)) {
			sb.append(" AND account_money <=" + maxMoney);
		}
		if (StringUtils.isNotBlank(beginTime)) {
			sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') >= '" + beginTime + "'");
		}
		if (StringUtils.isNotBlank(endTime)) {
			sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') <= '" + endTime + "'");
		}
		sb.append(
				" GROUP BY ACCOUNT_BODY_CARD,account_rival ) df where df.TOTALMONEY>0  AND df.TOTALNUMBER>0 and df.inNumber>0 ");
		if (StringUtils.isNotBlank(sortName)) {
			sb.append(" order by " + sortName + " " + sortOrder);
		}

		List<Map<String, Object>> maps = jdbcTemplate.queryForList(sb.toString());
		CollectionsUtils.getSortList(maps, "INMONEY");
		Query query = this.entityManager.createNativeQuery(sb.toString());
		// 将查询结果集转为Map
		query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		// 设置分页
		query.setFirstResult((page - 1) * size);
		query.setMaxResults(size);
		// 获取查询结果集
		List<Map<String, Object>> list = query.getResultList();
		PageResult pageResult = new PageResult(Long.valueOf(maps.size()), list, CollectionsUtils.getLimitTen(maps));
		return pageResult;
	}

	/**
	 *
	 * @author 刘中华
	 * @date 2018/12/4 11:30
	 * @param [casemanageId,
	 *            account_body_card]
	 * @return java.util.Map<java.lang.String,java.lang.Object>
	 * @Description 流出 主体统计
	 */
	public PageResult getOutStreamBodyCount(String minMoney, String maxMoney, String casemanageId,
			String account_body_card, String rivalName, Integer page, Integer size, String beginTime, String endTime,
			String sortName, String sortOrder) {
		String whichTable = caseManageService.getWhichTable(casemanageId);
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT df.*, ");
		sb.append(" concat( ROUND( df.inMoney / df.totalMoney * 100, 2 ), '%' )  AS inMoneyPer,");
		sb.append(" concat( ROUND( df.outMoney / df.totalMoney * 100, 2 ), '%' )  AS outMoneyPer,");
		sb.append(" df.inMoney - df.outMoney AS PureInMoney,");
		sb.append(" df.outMoney - df.inMoney AS PureOutMoney,");
		sb.append(" concat( ROUND( df.outNumber / df.totalNumber * 100, 2 ), '%' )  AS outNumberPer,");
		sb.append(" concat( ROUND( df.inNumber / df.totalNumber * 100, 2 ), '%' )  AS inNumberPer FROM (");
		sb.append(" SELECT MAX(ACCOUNT_BODY) as ACCOUNT_BODY,ACCOUNT_BODY_CARD,account_rival,SUM( abs(account_money) ) AS totalMoney,COUNT( 1 ) AS totalNumber,");
		sb.append(" COALESCE( sum( CASE WHEN sign = '0' THEN abs(account_money) END ), 0 ) AS inMoney,");
		sb.append(" COALESCE( sum( CASE WHEN sign = '1' THEN abs(account_money) END ), 0 ) AS outMoney,");
		sb.append(" COUNT( CASE WHEN sign = '0' THEN 1 END ) AS inNumber,");
		sb.append(" COUNT( CASE WHEN sign = '1' THEN 1 END ) AS outNumber ");
		sb.append(" FROM "+whichTable+" WHERE  account_rival_card IS NOT NULL AND casemanage_id = '" + casemanageId
				+ "' AND account_rival is not NULL ");
		if (StringUtils.isNotBlank(account_body_card)) {
			if (account_body_card.split(",").length < 1000)
				sb.append(" AND account_body_card in ( " + account_body_card + " )");
		} else {
			sb.append(" AND 1=0");
		}
		if (StringUtils.isNotBlank(rivalName)) {
			sb.append(" AND account_rival like '%" + rivalName + "%'");
		}
		if (StringUtils.isNotBlank(minMoney)) {
			sb.append(" AND account_money >=" + minMoney);
		}
		if (StringUtils.isNotBlank(maxMoney)) {
			sb.append(" AND account_money <=" + maxMoney);
		}
		if (StringUtils.isNotBlank(beginTime)) {
			sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') >= '" + beginTime + "'");
		}
		if (StringUtils.isNotBlank(endTime)) {
			sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') <= '" + endTime + "'");
		}
		sb.append(" GROUP BY ACCOUNT_BODY_CARD,account_rival ) df where df.TOTALMONEY>0  AND df.TOTALNUMBER>0 and df.outNumber>0 ");
		if (StringUtils.isNotBlank(sortName)) {
			sb.append(" order by " + sortName + " " + sortOrder);
		}
		List<Map<String, Object>> maps = jdbcTemplate.queryForList(sb.toString());
		CollectionsUtils.getSortList(maps, "INMONEY");
		Query query = this.entityManager.createNativeQuery(sb.toString());
		// 将查询结果集转为Map
		query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		// 设置分页
		query.setFirstResult((page - 1) * size);
		query.setMaxResults(size);
		// 获取查询结果集
		List<Map<String, Object>> list = query.getResultList();
		PageResult pageResult = new PageResult(Long.valueOf(maps.size()), list, CollectionsUtils.getLimitTen(maps));
		return pageResult;
	}

	/**
	 *
	 * @author 刘中华
	 * @date 2018/12/4 11:30
	 * @param [casemanageId,
	 *            account_body_card]
	 * @return java.util.Map<java.lang.String,java.lang.Object>
	 * @Description 流入/流出 日期统计
	 */
	public PageResult getInOutStreamTimeCount(String minMoney, String maxMoney, String casemanageId,
			String account_body_card, String rivalName, Integer page, Integer size, String beginTime, String endTime,
			String sortName, String sortOrder) {
		String whichTable = caseManageService.getWhichTable(casemanageId);
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT df.*, ");
		sb.append(" concat( ROUND( df.inMoney / df.totalMoney * 100, 2 ), '%' )  AS inMoneyPer,");
		sb.append(" concat( ROUND( df.outMoney / df.totalMoney * 100, 2 ), '%' )  AS outMoneyPer,");
		sb.append(" df.inMoney - df.outMoney AS PureInMoney,");
		sb.append(" df.outMoney - df.inMoney AS PureOutMoney,");
		sb.append(" concat( ROUND( df.outNumber / df.totalNumber * 100, 2 ), '%' )  AS outNumberPer,");
		sb.append(" concat( ROUND( df.inNumber / df.totalNumber * 100, 2 ), '%' )  AS inNumberPer FROM (");
		sb.append(
				" SELECT MAX(ACCOUNT_BODY) as ACCOUNT_BODY,MAX(ACCOUNT_RIVAL) as ACCOUNT_RIVAL,account_body_card,to_char(TRADE_DATE,'yyyy-mm-dd') AS DATETIME,SUM( abs(account_money) ) AS totalMoney,COUNT( BILL_ID ) AS totalNumber,");
		sb.append(" COALESCE( sum( CASE WHEN sign = '0' THEN abs(account_money) END ), 0 ) AS inMoney,");
		sb.append(" COALESCE( sum( CASE WHEN sign = '1' THEN abs(account_money) END ), 0 ) AS outMoney,");
		sb.append(" COUNT( CASE WHEN sign = '0' THEN 1 END ) AS inNumber,");
		sb.append(" COUNT( CASE WHEN sign = '1' THEN 1 END ) AS outNumber ");
		sb.append(" FROM "+whichTable+" WHERE  casemanage_id = '" + casemanageId
				+ "'  AND TRADE_DATE is not null AND account_rival_card IS NOT NULL ");
		if (StringUtils.isNotBlank(account_body_card)) {
			if (account_body_card.split(",").length < 1000)
				sb.append(" AND account_body_card in ( " + account_body_card + " )");
		} else {
			sb.append(" AND 1=0");
		}
		if (StringUtils.isNotBlank(rivalName)) {
			sb.append(" AND account_rival like '%" + rivalName + "%'");
		}
		if (StringUtils.isNotBlank(minMoney)) {
			sb.append(" AND account_money >=" + minMoney);
		}
		if (StringUtils.isNotBlank(maxMoney)) {
			sb.append(" AND account_money <=" + maxMoney);
		}
		if (StringUtils.isNotBlank(beginTime)) {
			sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') >= '" + beginTime + "'");
		}
		if (StringUtils.isNotBlank(endTime)) {
			sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') <= '" + endTime + "'");
		}
		sb.append(
				" GROUP BY account_body_card,to_char(TRADE_DATE,'yyyy-mm-dd') ORDER BY to_char(TRADE_DATE,'yyyy-mm-dd')  ) df where  df.TOTALMONEY>0  AND df.TOTALNUMBER>0");
		sb.append(" ORDER BY INMONEY DESC ");

		Query query = this.entityManager.createNativeQuery(sb.toString());
		// 将查询结果集转为Map
		query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		// 设置分页
		query.setFirstResult(0);
		query.setMaxResults(20);
		// 获取查询结果集
		List<Map<String, Object>> list = query.getResultList();
		PageResult pageResult = new PageResult(null, null, list);
		return pageResult;
	}

	/**
	 *
	 * @author 刘中华
	 * @date 2018/12/4 11:30
	 * @param [casemanageId,
	 *            account_body_card]
	 * @return java.util.Map<java.lang.String,java.lang.Object>
	 * @Description 流入/流出 日期统计
	 */
	public PageResult getInOutStreamTimeCount_easyui(DealFlowQueryDto df) {
		String whichTable = caseManageService.getWhichTable(df.getCaseId());
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT df.*, ");
		sb.append(" concat( ROUND( df.inMoney / df.totalMoney * 100, 2 ), '%' )  AS inMoneyPer,");
		sb.append(" concat( ROUND( df.outMoney / df.totalMoney * 100, 2 ), '%' )  AS outMoneyPer,");
		sb.append(" df.inMoney - df.outMoney AS PureInMoney,");
		sb.append(" df.outMoney - df.inMoney AS PureOutMoney,");
		sb.append(" concat( ROUND( df.outNumber / df.totalNumber * 100, 2 ), '%' )  AS outNumberPer,");
		sb.append(" concat( ROUND( df.inNumber / df.totalNumber * 100, 2 ), '%' )  AS inNumberPer FROM (");
		sb.append(
				" SELECT to_char(TRADE_DATE,'yyyy-mm-dd') AS DATETIME,SUM( abs(account_money) ) AS totalMoney,COUNT( BILL_ID ) AS totalNumber,");
		sb.append(" COALESCE( sum( CASE WHEN sign = '0' THEN abs(account_money) END ), 0 ) AS inMoney,");
		sb.append(" COALESCE( sum( CASE WHEN sign = '1' THEN abs(account_money) END ), 0 ) AS outMoney,");
		sb.append(" COUNT( CASE WHEN sign = '0' THEN 1 END ) AS inNumber,");
		sb.append(" COUNT( CASE WHEN sign = '1' THEN 1 END ) AS outNumber ");
		sb.append(" FROM "+whichTable+" WHERE  casemanage_id = '" + df.getCaseId()
				+ "'  AND TRADE_DATE is not null AND account_rival_card IS NOT NULL ");
		if (StringUtils.isNotBlank(df.getCardList())) {
			if (df.getCardList().split(",").length < 1000)
				sb.append(" AND account_body_card in ( " + df.getCardList() + " )");
		} else {
			sb.append(" AND 1=0");
		}
		if (StringUtils.isNotBlank(df.getRivalName())) {
			sb.append(" AND account_rival like '%" + df.getRivalName() + "%'");
		}
		if (StringUtils.isNotBlank(df.getMinMoney())) {
			sb.append(" AND account_money >=" + df.getMinMoney());
		}
		if (StringUtils.isNotBlank(df.getMaxMoney())) {
			sb.append(" AND account_money <=" + df.getMaxMoney());
		}
		if (StringUtils.isNotBlank(df.getBeginTime())) {
			sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') >= '" + df.getBeginTime() + "'");
		}
		if (StringUtils.isNotBlank(df.getEndTime())) {
			sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd HH24:MI:SS') <= '" + df.getEndTime() + " 23:59:59'");
		}
		sb.append(" GROUP BY to_char(TRADE_DATE,'yyyy-mm-dd') ORDER BY to_char(TRADE_DATE,'yyyy-mm-dd')  ) df where  df.TOTALMONEY>0  AND df.TOTALNUMBER>0");
		if (StringUtils.isNotBlank(df.getSort())) {
			sb.append(" order by " + df.getSort() + " " + df.getOrder());
		}
		Query query = this.entityManager.createNativeQuery(sb.toString());
		// 获取总结果集
		List<Map<String, Object>> maps = query.getResultList();
		// 将查询结果集转为Map
		query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		// 设置分页
		query.setFirstResult((df.getPage() - 1) * df.getRows());
		query.setMaxResults(df.getRows());
		// 获取查询结果集
		List<Map<String, Object>> list = query.getResultList();
		PageResult pageResult = new PageResult(Long.valueOf(maps.size()), list);
		return pageResult;
	}

	/**
	 *
	 * @author 刘中华
	 * @date 2018/12/4 11:30
	 * @param [casemanageId,
	 *            account_body_card]
	 * @return java.util.Map<java.lang.String,java.lang.Object>
	 * @Description 流入/流出 月统计
	 */
	public PageResult getInOutStreamMonthCount(String minMoney, String maxMoney, String casemanageId,
			String account_body_card, String rivalName, Integer page, Integer size, String beginTime, String endTime,
			String sortName, String sortOrder) {
		String whichTable = caseManageService.getWhichTable(casemanageId);
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT df.*, ");
		sb.append(" concat( ROUND( df.inMoney / df.totalMoney * 100, 2 ), '%' )  AS inMoneyPer,");
		sb.append(" concat( ROUND( df.outMoney / df.totalMoney * 100, 2 ), '%' )  AS outMoneyPer,");
		sb.append(" df.inMoney - df.outMoney AS PureInMoney,");
		sb.append(" df.outMoney - df.inMoney AS PureOutMoney,");
		sb.append(" concat( ROUND( df.outNumber / df.totalNumber * 100, 2 ), '%' )  AS outNumberPer,");
		sb.append(" concat( ROUND( df.inNumber / df.totalNumber * 100, 2 ), '%' )  AS inNumberPer FROM (");
		sb.append(
				" SELECT MAX(ACCOUNT_BODY) as ACCOUNT_BODY,MAX( ACCOUNT_RIVAL ) AS ACCOUNT_RIVAL,account_body_card,to_char(TRADE_DATE,'yyyy-mm') AS month,SUM( abs(account_money) ) AS totalMoney,COUNT( 1 ) AS totalNumber,");
		sb.append(" COALESCE( sum( CASE WHEN sign = '0' THEN abs(account_money) END ), 0 ) AS inMoney,");
		sb.append(" COALESCE( sum( CASE WHEN sign = '1' THEN abs(account_money) END ), 0 ) AS outMoney,");
		sb.append(" COUNT( CASE WHEN sign = '0' THEN 1 END ) AS inNumber,");
		sb.append(" COUNT( CASE WHEN sign = '1' THEN 1 END ) AS outNumber ");
		sb.append(" FROM "+whichTable+" WHERE  account_rival_card IS NOT NULL AND casemanage_id = '" + casemanageId
				+ "'  AND TRADE_DATE is not null");
		if (StringUtils.isNotBlank(account_body_card)) {
			if (account_body_card.split(",").length < 1000)
				sb.append(" AND account_body_card in ( " + account_body_card + " )");
		} else {
			sb.append(" AND 1=0");
		}
		if (StringUtils.isNotBlank(rivalName)) {
			sb.append(" AND account_rival like '%" + rivalName + "%'");
		}
		if (StringUtils.isNotBlank(minMoney)) {
			sb.append(" AND account_money >=" + minMoney);
		}
		if (StringUtils.isNotBlank(maxMoney)) {
			sb.append(" AND account_money <=" + maxMoney);
		}
		if (StringUtils.isNotBlank(beginTime)) {
			sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') >= '" + beginTime + "'");
		}
		if (StringUtils.isNotBlank(endTime)) {
			sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') <= '" + endTime + "'");
		}
		sb.append(
				" GROUP BY account_body_card,to_char(TRADE_DATE,'yyyy-mm') ORDER BY to_char(TRADE_DATE,'yyyy-mm') ) df where  df.TOTALMONEY>0  AND df.TOTALNUMBER>0");
		sb.append(" ORDER BY INMONEY DESC ");

		Query query = this.entityManager.createNativeQuery(sb.toString());
		// 将查询结果集转为Map
		query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		// 设置分页
		query.setFirstResult(0);
		query.setMaxResults(20);
		// 获取查询结果集
		List<Map<String, Object>> list = query.getResultList();
		PageResult pageResult = new PageResult(null, null, list);
		return pageResult;
	}
	public PageResult getInOutStreamMonthCount_easyui(DealFlowQueryDto df) {
		String whichTable = caseManageService.getWhichTable(df.getCaseId());
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT df.*, ");
		sb.append(" concat( ROUND( df.inMoney / df.totalMoney * 100, 2 ), '%' )  AS inMoneyPer,");
		sb.append(" concat( ROUND( df.outMoney / df.totalMoney * 100, 2 ), '%' )  AS outMoneyPer,");
		sb.append(" df.inMoney - df.outMoney AS PureInMoney,");
		sb.append(" df.outMoney - df.inMoney AS PureOutMoney,");
		sb.append(" concat( ROUND( df.outNumber / df.totalNumber * 100, 2 ), '%' )  AS outNumberPer,");
		sb.append(" concat( ROUND( df.inNumber / df.totalNumber * 100, 2 ), '%' )  AS inNumberPer FROM (");
		sb.append(
				" SELECT ACCOUNT_RIVAL , account_rival_card ,to_char(TRADE_DATE,'yyyy-mm') AS month,SUM( abs(account_money) ) AS totalMoney,COUNT( 1 ) AS totalNumber,");
		sb.append(" COALESCE( sum( CASE WHEN sign = '0' THEN abs(account_money) END ), 0 ) AS inMoney,");
		sb.append(" COALESCE( sum( CASE WHEN sign = '1' THEN abs(account_money) END ), 0 ) AS outMoney,");
		sb.append(" COUNT( CASE WHEN sign = '0' THEN 1 END ) AS inNumber,");
		sb.append(" COUNT( CASE WHEN sign = '1' THEN 1 END ) AS outNumber ");
		sb.append(" FROM "+whichTable+" WHERE  casemanage_id = '" + df.getCaseId()
				+ "'  AND  TRADE_DATE is not null AND account_rival_card IS NOT NULL ");
		if (StringUtils.isNotBlank(df.getCardList())) {
			if (df.getCardList().split(",").length < 1000)
				sb.append(" AND account_body_card in ( " + df.getCardList() + " )");
		} else {
			sb.append(" AND 1=0");
		}
		if (StringUtils.isNotBlank(df.getRivalName())) {
			sb.append(" AND account_rival like '%" + df.getRivalName() + "%'");
		}
		if (StringUtils.isNotBlank(df.getMinMoney())) {
			sb.append(" AND account_money >=" + df.getMinMoney());
		}
		if (StringUtils.isNotBlank(df.getMaxMoney())) {
			sb.append(" AND account_money <=" + df.getMaxMoney());
		}
		if (StringUtils.isNotBlank(df.getBeginTime())) {
			sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') >= '" + df.getBeginTime() + "'");
		}
		if (StringUtils.isNotBlank(df.getEndTime())) {
			sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') <= '" + df.getEndTime() + "'");
		}
		sb.append(
				" GROUP BY account_rival_card,ACCOUNT_RIVAL,to_char(TRADE_DATE,'yyyy-mm') ORDER BY to_char(TRADE_DATE,'yyyy-mm') ) df where  df.TOTALMONEY>0  AND df.TOTALNUMBER>0");
		if (StringUtils.isNotBlank(df.getSort())) {
			sb.append(" order by " + df.getSort() + " " + df.getOrder());
		}
		Query query = this.entityManager.createNativeQuery(sb.toString());
		// 获取总结果集
		List<Map<String, Object>> maps = query.getResultList();
		// 将查询结果集转为Map
		query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		// 设置分页
		query.setFirstResult((df.getPage() - 1) * df.getRows());
		query.setMaxResults(df.getRows());
		// 获取查询结果集
		List<Map<String, Object>> list = query.getResultList();
		PageResult pageResult = new PageResult(Long.valueOf(maps.size()), list);
		return pageResult;
	}

	/**
	 * 相互资金转移
	 * @param checkedAccount
	 * @param caseId
	 * @param beginTime
	 * @param endTime
	 * @param minMoney
	 * @param maxMoney
	 * @return
	 */
	public Map<String,Object> getMoneyFlow(List<String> checkedAccount,String caseId,String beginTime,String endTime,Integer minMoney,Integer maxMoney){
        String whichTable = caseManageService.getWhichTable(caseId);
		if (checkedAccount==null||checkedAccount.size()<1)return null;
		List<DealFlow> list = new ArrayList<>();
		Set<String> resultList = new HashSet<>();
		StringBuffer cardList = new StringBuffer();
		for (String str:checkedAccount){
			String[] split = str.split("_");
			DealFlow dealFlow = new DealFlow();
			dealFlow.setAccountBody(split[0]);
			dealFlow.setAccountBodyCard(split[1]);
			cardList.append("or account_rival_card ='"+split[1]+"' ");
			resultList.add(split[0].contains("未知")?split[1]:split[0]);
			list.add(dealFlow);
		}
		String ccList = cardList.toString().substring(2);
        List<String> collect = list.stream().map(DealFlow::getAccountBodyCard).collect(Collectors.toList());
        Map<String, List<DealFlow>> groupBy = list.stream().collect(Collectors.groupingBy(DealFlow::getAccountBody));
        List<DealFlow> mapList = new ArrayList<>();
        for (String s : groupBy.keySet()) {
            List<String> dealFlows = groupBy.get(s).stream().map(DealFlow::getAccountBodyCard).collect(Collectors.toList());
            String ss = "select ACCOUNT_BODY,ACCOUNT_BODY_CARD,ACCOUNT_RIVAL, ACCOUNT_RIVAL_CARD,ACCOUNT_MONEY,SIGN,TRADE_DATE from "+whichTable+" where CASEMANAGE_ID = '"+caseId+"' and account_body_card in ('"+StringUtils.join(dealFlows,"','")+"')" +
                    " and ("+ccList+") and account_rival_card not in ('"+StringUtils.join(dealFlows,"','")+"')";
			if (minMoney!=null&&minMoney!=0) {
				ss+=" AND account_money >=" + minMoney;
			}
			if (maxMoney!=null&&maxMoney!=0) {
				ss+=" AND account_money <=" + maxMoney;
			}
			if (StringUtils.isNotBlank(beginTime)) {
				ss+=" AND to_char(TRADE_DATE,'yyyy-mm-dd') >= '" + beginTime + "'";
			}
			if (StringUtils.isNotBlank(endTime)) {
				ss+=" AND to_char(TRADE_DATE,'yyyy-mm-dd') <= '" + endTime + "'";
			}
			ss+=" order by TRADE_DATE";
            mapList.addAll(jdbcTemplate.query(ss, new BeanPropertyRowMapper<DealFlow>(DealFlow.class)));
        }
        SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd");
        List<Canvas> cas = new ArrayList<>();
        for (DealFlow map:mapList){
            Canvas canvas = new Canvas();
            canvas.setFlag(map.getSign());
            canvas.setMoney(map.getAccountMoney());
            String format = sd.format(map.getTradeDate());
            canvas.setTime(format);
            if (map.getSign()==0){
                canvas.setFrom(map.getAccountRival()==null?map.getAccountRivalCard():map.getAccountRival());
                canvas.setTo(map.getAccountBody()==null?map.getAccountBodyCard():map.getAccountBody());
            }else if (map.getSign()==1){
                canvas.setFrom(map.getAccountBody()==null?map.getAccountBodyCard():map.getAccountBody());
                canvas.setTo(map.getAccountRival()==null?map.getAccountRivalCard():map.getAccountRival());
            }
            cas.add(canvas);
        }
        if (cas.size()>500)cas = cas.subList(0,500);
		List<String> listtt = null;
		if (resultList.size()>100){
			listtt = new ArrayList<>(resultList);
			listtt = listtt.subList(0,100);
		}

        Map<String,Object> resultMap = new HashMap<>();
        resultMap.put("flowArr",cas);
        resultMap.put("userArr",listtt==null?resultList:listtt);
        resultMap.put("excel",mapList);

        return resultMap;
	}

	/**
	 * 个人资金转移
 	 * @param checkedAccount
	 * @param caseId
	 * @param beginTime
	 * @param endTime
	 * @param minMoney
	 * @param maxMoney
	 * @return
	 */
	public Map<String,Object> moneyFlowPersonal(DealFlowVo vo){
		List<String> resultList = new ArrayList<String>();
		List<DealFlow> mapList = zjlxfx(vo);
		if(mapList!=null) {
			Collections.sort(mapList, new Comparator<DealFlow>() {
	            @Override
	            public int compare(DealFlow o1, DealFlow o2) {
	                if (o1.getTradeDate().compareTo(o2.getTradeDate()) == 0) {
	                    return o1.getTradeDate().compareTo(o2.getTradeDate());
	                } else {
	                    return o1.getTradeDate().compareTo(o2.getTradeDate());
	                }
	            }

	        });
		}
		
		List<DealFlow> afterList = new ArrayList<>();
		if (mapList.size()>400){
			afterList = mapList.subList(0,400);
		}else {
			afterList=mapList;
		}
		
		for (DealFlow del: afterList) {
			String body = del.getAccountBody()==null?del.getAccountBodyCard():del.getAccountBody();
			if(!resultList.contains(body)) resultList.add(body);
			String rival = del.getAccountRival()==null?del.getAccountRivalCard():del.getAccountRival();
			if(!resultList.contains(rival)) resultList.add(rival);
		}

        SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat sd2 = new SimpleDateFormat("hh:mm:ss");
        List<Canvas> cas = new ArrayList<>();
        for (DealFlow map:afterList){
            Canvas canvas = new Canvas();
            canvas.setMoney(map.getAccountMoney());
            String date = sd.format(map.getTradeDate());
            String time = sd2.format(map.getTradeDate());
            canvas.setDate(date);
            canvas.setTime(time);
            if(vo.getSign()!=null && vo.getSign()==0) {
            	canvas.setTo(map.getAccountBody()==null?map.getAccountBodyCard():map.getAccountBody());
            	canvas.setFrom(map.getAccountRival()==null?map.getAccountRivalCard():map.getAccountRival());
            }else {
            	canvas.setFrom(map.getAccountBody()==null?map.getAccountBodyCard():map.getAccountBody());
            	canvas.setTo(map.getAccountRival()==null?map.getAccountRivalCard():map.getAccountRival());
            }
            cas.add(canvas);
        }
		List<String> list = null;
		if (resultList.size()>90){
			list = new ArrayList<>(resultList);
			list = list.subList(0,90);
		}

        Map<String,Object> resultMap = new HashMap<>();
        resultMap.put("flowArr",cas);
        resultMap.put("userArr",list==null?resultList:list);
        return resultMap;
	}
	/**
	 *
	 * @author 刘中华
	 * @date 2018/12/4 11:30
	 * @param [casemanageId,
	 *            account_body_card]
	 * @return java.util.Map<java.lang.String,java.lang.Object>
	 * @Description 流入/流出 摘要统计
	 */
	public PageResult getInOutStreamAbstractCount(String minMoney, String maxMoney, String casemanageId,
			String account_body_card, String rivalName, Integer page, Integer size, String beginTime, String endTime,
			String sortName, String sortOrder,Integer showType) {
		String whichTable = caseManageService.getWhichTable(casemanageId);
		PageResult pageResult = null;
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT df2.*, ");
		sb.append(
				" concat( to_char(ROUND( df2.transactiontimes / df1.sumA * 100, 2 ),'fm99990.00'), '%' )  AS  transactiontimesPer, ");
		sb.append(
				" concat( to_char(ROUND( df2.transactionmoney / df1.sumB * 100, 2 ),'fm99990.00'), '%' )  AS  transactionMoneyPer ");
		sb.append(" FROM ");
		sb.append(" ( SELECT count( 1 ) sumA, sum( abs(account_money) ) sumB FROM "+whichTable);
		sb.append(" WHERE  account_rival_card IS NOT NULL AND casemanage_id = '" + casemanageId + "'");
		if (StringUtils.isNotBlank(account_body_card)) {
			if (account_body_card.split(",").length < 1000){
				sb.append(" AND account_body_card in ( " + account_body_card + " )");
			}
		} else {
			pageResult = new PageResult(0L, new ArrayList<Map<String, Object>>(), new ArrayList<Map<String,Object>>());
			return pageResult;
		}
		if (StringUtils.isNotBlank(rivalName)) {
			sb.append(" AND account_rival like '%" + rivalName + "%'");
		}
		if (StringUtils.isNotBlank(minMoney)) {
			sb.append(" AND account_money >=" + minMoney);
		}
		if (StringUtils.isNotBlank(maxMoney)) {
			sb.append(" AND account_money <=" + maxMoney);
		}
		if (StringUtils.isNotBlank(beginTime)) {
			sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') >= '" + beginTime + "'");
		}
		if (StringUtils.isNotBlank(endTime)) {
			sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') <= '" + endTime + "'");
		}
		sb.append(" ) df1,");
		sb.append(" ( SELECT content_abstract, count( 1 ) transactiontimes, sum( abs(account_money) ) transactionmoney FROM "+whichTable );
		sb.append(" WHERE account_rival_card IS NOT NULL AND casemanage_id = '" + casemanageId+"'");
		if (StringUtils.isNotBlank(account_body_card)) {
			if (account_body_card.split(",").length < 1000){
				sb.append(" AND account_body_card in ( " + account_body_card + " )");
			}
		}else{
			pageResult = new PageResult(0L, new ArrayList<Map<String, Object>>(), new ArrayList<Map<String,Object>>());
			return pageResult;
		}
		if (StringUtils.isNotBlank(rivalName)) {
			sb.append(" AND account_rival like '%" + rivalName + "%'");
		}
		if (StringUtils.isNotBlank(beginTime)) {
			sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') >= '" + beginTime + "'");
		}
		if (StringUtils.isNotBlank(endTime)) {
			sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') <= '" + endTime + "'");
		}
		sb.append(" GROUP BY content_abstract ) df2");
		if (StringUtils.isNotBlank(sortName)) {
			sb.append(" order by " + sortName + " " + sortOrder);
		}
		sb.append(" ORDER BY TRANSACTIONMONEY DESC ");
		Query query = this.entityManager.createNativeQuery(sb.toString());
		// 将查询结果集转为Map
		query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		// 设置分页
		query.setFirstResult(0);
		query.setMaxResults(20);
		// 获取查询结果集
		List<Map<String, Object>> list = query.getResultList();
		pageResult = new PageResult(null, null, list);
		return pageResult;
	}
    public PageResult getInOutStreamAbstractCount_easyui(DealFlowQueryDto df) {
        String whichTable = caseManageService.getWhichTable(df.getCaseId());
        PageResult pageResult = null;
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT df2.*, ");
        sb.append(
                " concat( to_char(ROUND( df2.transactiontimes / df1.sumA * 100, 2 ),'fm99990.00'), '%' )  AS  transactiontimesPer, ");
        sb.append(
                " concat( to_char(ROUND( df2.transactionmoney / df1.sumB * 100, 2 ),'fm99990.00'), '%' )  AS  transactionMoneyPer ");
        sb.append(" FROM ");
        sb.append(" ( SELECT count( 1 ) sumA, sum( abs(account_money) ) sumB FROM "+whichTable);
        sb.append(" WHERE  casemanage_id = '" + df.getCaseId() + "'");
        if (StringUtils.isNotBlank(df.getCardList())) {
            if (df.getCardList().split(",").length < 1000){
                sb.append(" AND account_body_card in ( " + df.getCardList() + " )");
            }
        } else {
            pageResult = new PageResult(0L, new ArrayList<Map<String, Object>>(), new ArrayList<Map<String,Object>>());
            return pageResult;
        }
        if (StringUtils.isNotBlank(df.getRivalName())) {
            sb.append(" AND account_rival like '%" + df.getRivalName() + "%'");
        }
        if (StringUtils.isNotBlank(df.getMinMoney())) {
            sb.append(" AND account_money >=" + df.getMinMoney());
        }
        if (StringUtils.isNotBlank(df.getMaxMoney())) {
            sb.append(" AND account_money <=" + df.getMaxMoney());
        }
        if (StringUtils.isNotBlank(df.getBeginTime())) {
            sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') >= '" + df.getBeginTime() + "'");
        }
        if (StringUtils.isNotBlank(df.getEndTime())) {
            sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') <= '" + df.getEndTime() + "'");
        }
        sb.append(" ) df1,");
        sb.append(" ( SELECT content_abstract, count( 1 ) transactiontimes, sum( abs(account_money) ) transactionmoney FROM "+whichTable );
        sb.append(" WHERE casemanage_id = '" + df.getCaseId()+"'");
        if (StringUtils.isNotBlank(df.getCardList())) {
            if (df.getCardList().split(",").length < 1000){
                sb.append(" AND account_body_card in ( " + df.getCardList() + " )");
            }
        }else{
            pageResult = new PageResult(0L, new ArrayList<Map<String, Object>>(), new ArrayList<Map<String,Object>>());
            return pageResult;
        }
        if (StringUtils.isNotBlank(df.getRivalName())) {
            sb.append(" AND account_rival like '%" + df.getRivalName() + "%'");
        }
        if (StringUtils.isNotBlank(df.getBeginTime())) {
            sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') >= '" + df.getBeginTime() + "'");
        }
        if (StringUtils.isNotBlank(df.getEndTime())) {
            sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') <= '" + df.getEndTime() + "'");
        }
        sb.append(" GROUP BY content_abstract ) df2");
		if (StringUtils.isNotBlank(df.getSort())) {
			sb.append(" order by " + df.getSort() + " " + df.getOrder());
		}else {
			sb.append(" order by TRANSACTIONMONEY desc ");
		}
        Query query = this.entityManager.createNativeQuery(sb.toString());
		// 获取总结果集
		List<Map<String, Object>> maps = query.getResultList();
        // 将查询结果集转为Map
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		// 设置分页
		query.setFirstResult((df.getPage() - 1) * df.getRows());
		query.setMaxResults(df.getRows());
        // 获取查询结果集
        List<Map<String, Object>> list = query.getResultList();
        pageResult = new PageResult(Long.valueOf(maps.size()), list);
        return pageResult;
    }

	/**
	 *
	 * 交易银行统计
	 * 
	 * @return
	 */
	public PageResult getBankStatisticsList(String minMoney, String maxMoney, String cards, String casemanageId,
			String rivalName, Integer page, Integer size, String startTime, String endTime, String sortName,
			String sortOrder) {
		String whichTable = caseManageService.getWhichTable(casemanageId);
		StringBuffer sb = new StringBuffer();
		sb.append("select count(*) account_count,ACCOUNT_RIVAL_CARD,\n"
				+ "        count(*)/(select count(*) from "+whichTable+" where account_body_number = account_body_number)*100 morethan,\n"
				+ "       sum(ABS(account_money)) money,\n"
				+ "       sum(ABS(account_money))/(select sum(ABS(account_money)) from "+whichTable+" where account_body_number = account_body_number)*100 moneyproportion,\n"
				+ "       MAX(INSTITUTION_NUMBER)	INSTITUTION_NUMBER, MAX(account_rival_bank) account_rival_bank, MAX(account_body_bank) account_body_bank " + "from "+whichTable+" tdf\n"
				+ " where is_delete=0 and casemanage_id = '" + casemanageId + "'");
		if (StringUtils.isNotBlank(cards)) {
			if (cards.split(",").length < 1000)
				sb.append(" AND account_body_card in ( " + cards + " )");
		} else {
			sb.append("AND 1=0");
		}
		if (StringUtils.isNotBlank(minMoney)) {
			sb.append(" AND account_money >=" + minMoney);
		}
		if (StringUtils.isNotBlank(maxMoney)) {
			sb.append(" AND account_money <=" + maxMoney);
		}
		if (StringUtils.isNotBlank(startTime)) {
			sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd')>='" + startTime + "'");
		}
		if (StringUtils.isNotBlank(endTime)) {
			sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd')<='" + endTime + "'");
		}
		if (StringUtils.isNotBlank(rivalName)) {
			sb.append(" AND (account_rival like '%" + rivalName + "%' or ACCOUNT_RIVAL_CARD like '%"+rivalName+"%')");
		}
		sb.append(" group by account_rival_card");
		if (StringUtils.isNotBlank(sortName)) {
			sb.append(" order by " + sortName + " " + sortOrder);
		}else {
			sb.append(" order by MONEY desc ");
		}
		List<Map<String, Object>> maps = jdbcTemplate.queryForList(sb.toString());
		CollectionsUtils.getSortList(maps, "MONEY");

		Query query = this.entityManager.createNativeQuery(sb.toString());
		// 将查询结果集转为Map
		query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		// 设置分页
		query.setFirstResult((page - 1) * size);
		query.setMaxResults(size);
		// 获取查询结果集
		List<Map<String, Object>> list = query.getResultList();
		for (Map<String, Object> mmp : list) {
			BigDecimal bd = (BigDecimal) mmp.get("MORETHAN");
			double v = bd.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
			mmp.put("MORETHAN", v);
			BigDecimal db = (BigDecimal) mmp.get("MONEYPROPORTION");
			double v1 = db.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
			mmp.put("MONEYPROPORTION", v1);
			BigDecimal bb = (BigDecimal) mmp.get("MONEY");
			BigDecimal abs = bb.abs();
			double m = abs.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
			mmp.put("MONEY", m);
		}
		PageResult pageResult = new PageResult(Long.valueOf(maps.size()), list, CollectionsUtils.getLimitTen(maps));
		return pageResult;

	}

	@Override
	protected BaseRepository<DealFlow> getBaseRepository() {
		return null;
	}

	public PageResult getInStreamAccountCount_detail(String caseId, DealflowVO dealFlow, int page, int size) {
		String whichTable = caseManageService.getWhichTable(caseId);
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT df.* ");
		sb.append(" FROM "+whichTable+" df WHERE  casemanage_id = '" + caseId + "' ");

		if (StringUtils.isNotBlank(dealFlow.getCardList())) {
			if (dealFlow.getCardList().split(",").length < 1000)
				sb.append(" AND account_body_card in ( " + dealFlow.getCardList() + " )");
		}
		if (dealFlow.getSign() != null) {
			sb.append(" AND sign = " + dealFlow.getSign());
		}
		if (StringUtils.isNotBlank(dealFlow.getAccountRival())) {
			sb.append(" AND ACCOUNT_RIVAL = '" + dealFlow.getAccountRival() + "'");
		}
		if (StringUtils.isNotBlank(dealFlow.getUuid())) {
			sb.append(" AND uuid = '" + dealFlow.getUuid() + "'");
		}
		if (StringUtils.isNotBlank(dealFlow.getAccountRivalCard())) {
			sb.append(" AND ACCOUNT_RIVAL_CARD = '" + dealFlow.getAccountRivalCard() + "'");
		}
		if (StringUtils.isNotBlank(dealFlow.getTimeVal())) {
			sb.append(" AND to_char(TRADE_DATE,'" + dealFlow.getTimeType() + "') = '" + dealFlow.getTimeVal() + "'");
		}
		if (StringUtils.isNotBlank(dealFlow.getContentAbstract())) {
			if(dealFlow.getContentAbstract().equals("null")) {
				sb.append(" AND CONTENT_ABSTRACT is null ");
			}else {
				sb.append(" AND CONTENT_ABSTRACT = '" + dealFlow.getContentAbstract() + "'");
			}
		}
		if (StringUtils.isNotBlank(dealFlow.getSort())) {
			sb.append(" order by " + dealFlow.getSort() + " " + dealFlow.getOrder());
		}
		List<Map<String, Object>> maps = jdbcTemplate.queryForList(sb.toString());

		Query query = this.entityManager.createNativeQuery(sb.toString());
		// 将查询结果集转为Map
		query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		// 设置分页
		query.setFirstResult((page-1) * size);
		query.setMaxResults(size);
		// 获取查询结果集
		List<Map<String, Object>> list = query.getResultList();
		PageResult pageResult = new PageResult(Long.valueOf(maps.size()), list);
		return pageResult;
	}

	/**
	 *
	 * @author 刘中华
	 * @date 2019/1/14 18:17
	 * @param [map]
	 * @return java.util.List
	 * @Description 交易主体卡号根据月份的流水图
	 */
	public List monthAnalysis(Map map) {
		String whichTable = caseManageService.getWhichTable(map.get("CASEMANAGE_ID").toString());
		StringBuffer sb2 = new StringBuffer();
		sb2.append(
				" select DISTINCT to_char( TRADE_DATE, 'yyyy' ) YEAR FROM "+whichTable+" WHERE TRADE_DATE IS NOT NULL  ");
		if (map.get("CASEMANAGE_ID") != null) {
			sb2.append(" AND CASEMANAGE_ID = '" + map.get("CASEMANAGE_ID") + "'");
		}
		if (map.get("ACCOUNT_BODY_CARD") != null) {
			sb2.append(" AND ACCOUNT_BODY_CARD = '" + map.get("ACCOUNT_BODY_CARD") + "'");
		}
		if (StringUtils.isNotBlank(map.get("beginTime").toString())) {
			sb2.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd')>='" + map.get("beginTime") + "'");
		}
		if (StringUtils.isNotBlank(map.get("endTime").toString())) {
			sb2.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd')<='" + map.get("endTime") + "'");
		}
		sb2.append(" ORDER BY to_char( TRADE_DATE, 'yyyy' )  ");
		List<Map<String, Object>> year = jdbcTemplate.queryForList(sb2.toString());
		List<String> year1 = year.stream().map(m -> (String) m.get("YEAR")).collect(Collectors.toList());
		List<String> list = new ArrayList<String>();

		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT to_char( TRADE_DATE, 'yyyy-mm-dd' ) DAY  FROM "+whichTable+" WHERE  TRADE_DATE IS NOT NULL  ");
		if (map.get("CASEMANAGE_ID") != null) {
			sb.append(" AND CASEMANAGE_ID = '" + map.get("CASEMANAGE_ID") + "'");
		}
		if (map.get("ACCOUNT_BODY_CARD") != null) {
			sb.append(" AND ACCOUNT_BODY_CARD = '" + map.get("ACCOUNT_BODY_CARD") + "'");
		}
		if (StringUtils.isNotBlank(map.get("beginTime").toString())) {
			sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd')>='" + map.get("beginTime") + "'");
		}
		if (StringUtils.isNotBlank(map.get("endTime").toString())) {
			sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd')<='" + map.get("endTime") + "'");
		}
		sb.append(" GROUP BY to_char( TRADE_DATE, 'yyyy-mm-dd' ) ORDER BY to_char( TRADE_DATE, 'yyyy-mm-dd' )  ");
		List<Map<String, Object>> maps = jdbcTemplate.queryForList(sb.toString());
		List<String> yearMonth = DateUtils.getYearMonth(year1);
		for (int i = 0; i < yearMonth.size(); i++) {
			list.add(yearMonth.get(i));
			for (int j = 0; j < maps.size(); j++) {
				Map<String, Object> m = maps.get(j);
				String day = (String) m.get("DAY");
				if (yearMonth.get(i).equals(day.substring(0, 7))) {
					list.add(day);
				}

			}

		}
		return list;
	}

	/**
	 * @Author 刘中华
	 * @Date 2019/1/15 10:32
	 * @Param [whereMap]
	 * @return java.util.List
	 * @Description 流水详情图
	 **/
	public PageResult getMonthAnalysisDetail(Map whereMap, int page) {
		String whichTable = caseManageService.getWhichTable(whereMap.get("CASEMANAGE_ID").toString());
		StringBuffer sb = new StringBuffer();
		String bodyQuery="select  * ";
		String countQuery="select  count(1) ";
		sb.append(" FROM  "+whichTable+"  WHERE 1=1 ");
		if (whereMap.get("CASEMANAGE_ID") != null) {
			sb.append(" AND CASEMANAGE_ID = '" + whereMap.get("CASEMANAGE_ID") + "'");
		}
		if (whereMap.get("ACCOUNT_BODY_CARD") != null) {
			sb.append(" AND ACCOUNT_BODY_CARD = '" + whereMap.get("ACCOUNT_BODY_CARD") + "'");
		}
		if (whereMap.get("TRADE_DATE") != null) {
			sb.append(" AND to_char(TRADE_DATE,'yyyy-MM-dd') = '" + whereMap.get("TRADE_DATE") + "'");
		}
		if (StringUtils.isNotBlank((String) whereMap.get("RIVALNAME"))) {
			sb.append(" AND account_rival like '%" + whereMap.get("RIVALNAME") + "%'");
		}
		if (StringUtils.isNotBlank((String) whereMap.get("minMoney"))) {
			sb.append(" AND account_money >= " + whereMap.get("minMoney"));
		}
		if (StringUtils.isNotBlank((String) whereMap.get("maxMoney"))) {
			sb.append(" AND account_money <= " + whereMap.get("maxMoney"));
		}
		if (StringUtils.isNotBlank((String) whereMap.get("beginTime"))) {
			sb.append(" AND to_char(TRADE_DATE,'yyyy-MM-dd') >= '" + whereMap.get("beginTime") + "'");
		}
		if (StringUtils.isNotBlank((String) whereMap.get("endTime"))) {
			sb.append(" AND to_char(TRADE_DATE,'yyyy-MM-dd') <= '" + whereMap.get("endTime") + "'");
		}
		long total = jdbcTemplate.queryForObject(countQuery+sb.toString(),long.class);

		Query query = this.entityManager.createNativeQuery(bodyQuery+sb.toString());
		// 将查询结果集转为Map
		query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		// 设置分页
		query.setFirstResult((page - 1) * 10);
		query.setMaxResults(10);
		// 获取查询结果集
		List<Map<String, Object>> maps = query.getResultList();

		Set<String> account_rival_card = maps.stream().map(m -> (String) m.get("ACCOUNT_RIVAL_CARD"))
				.collect(Collectors.toSet());
		List<Map<String, String>> mapList = new ArrayList<>();
		for (String m : account_rival_card) {
			Map<String, String> stringStringHashMap = new HashMap<>();
			HashMap<Object, Object> map = new HashMap<>();
			map.put("CASEMANAGE_ID", whereMap.get("CASEMANAGE_ID"));
			map.put("ACCOUNT_BODY_CARD", whereMap.get("ACCOUNT_BODY_CARD"));
			map.put("ACCOUNT_RIVAL_CARD", m);
			boolean addAgencyList = agencyService.isAddAgencyList(map);
			stringStringHashMap.put("ACCOUNT_RIVAL_CARD", m);
			if (addAgencyList) {
				stringStringHashMap.put("AGENCY", "1");// 说明加入了待办列表
			} else {
				boolean relatedBill = dealFlowService.isRelatedBill(map);
				if (!relatedBill) {
					stringStringHashMap.put("AGENCY", "2");// 说明无相关账单
				} else {
					stringStringHashMap.put("AGENCY", "3");// 说明有相关账单
				}
			}
			mapList.add(stringStringHashMap);
		}
		for (Map<String, Object> map : maps) {
			String ACCOUNT_RIVAL_CARD = (String) map.get("ACCOUNT_RIVAL_CARD");
			if (StringUtils.isBlank(ACCOUNT_RIVAL_CARD))
				continue;
			for (Map<String, String> m : mapList) {
				if (ACCOUNT_RIVAL_CARD.equals(m.get("ACCOUNT_RIVAL_CARD"))) {
					map.put("AGENCY", m.get("AGENCY"));
					break;
				}
			}
		}
		PageResult pageResult = new PageResult(total, maps);
		return pageResult;
	}
	public PageResult getMonthAnalysisDetail_easyui(MonthAnalysisDto dto) {
		String whichTable = caseManageService.getWhichTable(dto.getCaseId());
		StringBuffer sb = new StringBuffer();
		String bodyQuery="select  * ";
		String countQuery="select  count(1) ";
		sb.append(" FROM  "+whichTable+"  WHERE 1=1 ");
		if (dto.getCaseId() != null) {
			sb.append(" AND CASEMANAGE_ID = '" + dto.getCaseId() + "'");
		}
		if ( dto.getAccountBodyCard()!= null) {
			sb.append(" AND ACCOUNT_BODY_CARD = '" + dto.getAccountBodyCard() + "'");
		}
		if (dto.getTradeDate() != null) {
			sb.append(" AND to_char(TRADE_DATE,'yyyy-MM-dd') = '" +dto.getTradeDate() + "'");
		}
		if (StringUtils.isNotBlank(dto.getRivalName())) {
			sb.append(" AND account_rival like '%" + dto.getRivalName() + "%'");
		}
		if (StringUtils.isNotBlank(dto.getMinMoney())) {
			sb.append(" AND account_money >= " + dto.getMinMoney());
		}
		if (StringUtils.isNotBlank(dto.getMaxMoney())) {
			sb.append(" AND account_money <= " + dto.getMaxMoney());
		}
		if (StringUtils.isNotBlank(dto.getBeginTime())) {
			sb.append(" AND to_char(TRADE_DATE,'yyyy-MM-dd') >= '" + dto.getBeginTime() + "'");
		}
		if (StringUtils.isNotBlank(dto.getEndTime())) {
			sb.append(" AND to_char(TRADE_DATE,'yyyy-MM-dd') <= '" + dto.getEndTime() + "'");
		}
        if (StringUtils.isNotBlank(dto.getSort())) {
            sb.append(" order by " + dto.getSort() + " " + dto.getOrder());
        }
		long total = jdbcTemplate.queryForObject(countQuery+sb.toString(),long.class);

		Query query = this.entityManager.createNativeQuery(bodyQuery+sb.toString());
		// 将查询结果集转为Map
		query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		// 设置分页
		query.setFirstResult((dto.getPage() - 1) * dto.getRows());
		query.setMaxResults( dto.getRows());
		// 获取查询结果集
		List<Map<String, Object>> maps = query.getResultList();

		Set<String> account_rival_card = maps.stream().map(m -> (String) m.get("ACCOUNT_RIVAL_CARD"))
				.collect(Collectors.toSet());
		List<Map<String, String>> mapList = new ArrayList<>();
		for (String m : account_rival_card) {
			Map<String, String> stringStringHashMap = new HashMap<>();
			HashMap<Object, Object> map = new HashMap<>();
			map.put("CASEMANAGE_ID", dto.getCaseId());
			map.put("ACCOUNT_BODY_CARD", dto.getAccountBodyCard());
			map.put("ACCOUNT_RIVAL_CARD", m);
			boolean addAgencyList = agencyService.isAddAgencyList(map);
			stringStringHashMap.put("ACCOUNT_RIVAL_CARD", m);
			if (addAgencyList) {
				stringStringHashMap.put("AGENCY", "1");// 说明加入了待办列表
			} else {
				boolean relatedBill = dealFlowService.isRelatedBill(map);
				if (!relatedBill) {
					stringStringHashMap.put("AGENCY", "2");// 说明无相关账单
				} else {
					stringStringHashMap.put("AGENCY", "3");// 说明有相关账单
				}
			}
			mapList.add(stringStringHashMap);
		}
		for (Map<String, Object> map : maps) {
			String ACCOUNT_RIVAL_CARD = (String) map.get("ACCOUNT_RIVAL_CARD");
			if (StringUtils.isBlank(ACCOUNT_RIVAL_CARD))
				continue;
			for (Map<String, String> m : mapList) {
				if (ACCOUNT_RIVAL_CARD.equals(m.get("ACCOUNT_RIVAL_CARD"))) {
					map.put("AGENCY", m.get("AGENCY"));
					break;
				}
			}
		}
		PageResult pageResult = new PageResult(total, maps);
		return pageResult;
	}
	
	public Map<String, Object> moneyFlowPersonalD3(DealFlowVo vo){
		List<DealFlow> allList = zjlxfx(vo);
        Map<String, Object> hashMap = new HashMap<>();
        if (allList.size()>0){
            List<Map<String, Object>> nodes = new ArrayList<Map<String, Object>>();
            List<Map<String, Object>> links = new ArrayList<Map<String, Object>>();
            List<String> nodeNameList = new ArrayList<String>();
            for (int j=0;j<allList.size();j++){
            	DealFlow df = allList.get(j);
                /** 封装交易主题D3 **/
            	String nodeName = (StringUtils.isNotEmpty(df.getAccountBody()))?df.getAccountBody():df.getAccountBodyCard();
            	String nodeCard = df.getAccountBodyCard()==null?"":df.getAccountBodyCard();
            	String isSpecial = "0";
        		if (!nodeNameList.contains(nodeName) && StringUtils.isNotEmpty(nodeCard)){
        		    HashMap<String, Object> node = new HashMap<>();
        		    node.put("id",UUID.randomUUID().toString());
        		    node.put("name",nodeName);
        		    node.put("card",nodeCard);
        		    node.put("isSpecial", isSpecial);
        		    if("-1".equals(vo.getCheckedAccount()) || vo.getCheckedAccount().indexOf(nodeCard)>=0) {
        		    	node.put("width",100);
        		    	node.put("height",100);
        		    }else {
        		    	node.put("width",60);
        		    	node.put("height",60);
        		    }
        		    // 判断账户名称是否包含企业或公司
        		    if(com.mairuide._frame.utils.StringUtils.isNotEmpty(nodeName) && com.mairuide._frame.utils.StringUtils.isNotEmpty(nodeCard)) {
        		    	if(nodeName.contains("企业") || nodeName.contains("公司") 
        		    			|| nodeName.contains("支付宝") || nodeName.contains("财付通")
        		    			|| nodeCard.length() == 15 || nodeCard.length() == 17 ) {
        		    		if(isSpecial.equals("1")) {
        		    			//嫌疑单位账户
        		        		node.put("image","/plugins/d3/image/q2.png");
        		    		}else {
        		    			node.put("image","/plugins/d3/image/q1.png");
        		    		}
        		    	}else {
        		    		if(isSpecial.equals("1")) {
        		    			//嫌疑人账户
        		        		node.put("image","/plugins/d3/image/r2.png");
        		    		}else {
        		    			node.put("image","/plugins/d3/image/r1.png");
        		    		}
        		    	}
        		    }else {
        		    	node.put("image","/plugins/d3/image/r1.png");
        		    }
        		    nodes.add(node);
        		    nodeNameList.add(nodeName);
        		}
            }

            for (int j=0;j<allList.size();j++){
            	/** 封装交易对手D3 **/
            	DealFlow df = allList.get(j);
            	//封装交易对手 多循环一次是为了先封装交易主体，再封装对手
        		String nodeName2 = (StringUtils.isNotEmpty(df.getAccountRival())?df.getAccountRival():df.getAccountRivalCard());
        		String nodeCard2 = df.getAccountRivalCard();
        		if (!nodeNameList.contains(nodeName2) && StringUtils.isNotEmpty(nodeName2)){
        		    HashMap<String, Object> node = new HashMap<>();
        		    node.put("id",UUID.randomUUID().toString());
        		    node.put("name",nodeName2);
        		    node.put("card",nodeCard2);
        		    node.put("isSpecial", "0");
        			node.put("width",60);
        			node.put("height",60);
        			// 判断账户名称是否包含企业或公司
        		    if(com.mairuide._frame.utils.StringUtils.isNotEmpty(nodeName2) && com.mairuide._frame.utils.StringUtils.isNotEmpty(nodeCard2)) {
        		    	if(nodeName2.contains("企业") || nodeName2.contains("公司") 
        		    			|| nodeName2.contains("支付宝") || nodeName2.contains("财付通")
        		    			|| nodeCard2.length() == 15 || nodeCard2.length() == 17) {
        		    		node.put("image","/plugins/d3/image/q1.png");
        		    	}else {
        		    		node.put("image","/plugins/d3/image/r1.png");
        		    	}
        		    }else {
        		    	node.put("image","/plugins/d3/image/r1.png");
        		    }
        			
        		    nodes.add(node);
        		    nodeNameList.add(nodeName2);
        		}
            }
            /** 封装交易主体交易对手line start**/
            for (int j=0;j<allList.size();j++){
                DealFlow df = allList.get(j);
                String nodeName = (StringUtils.isNoneEmpty(df.getAccountBody())?df.getAccountBody():df.getAccountBodyCard());
                String nodeName2 = (StringUtils.isNotEmpty(df.getAccountRival()))?df.getAccountRival():df.getAccountRivalCard();
                if(StringUtils.isNotEmpty(nodeName) && StringUtils.isNotEmpty(nodeName2)) {
                	HashMap<String, Object> link = new HashMap<>();
                	for (Map<String, Object> m:nodes){
                		if(vo.getSign()!=null && vo.getSign()==0) {
                			if (nodeName.equals((String)m.get("name"))){
                				link.put("node2",m.get("id"));
                			}
                			if (nodeName2.equals((String)m.get("name"))){
                				link.put("node1",m.get("id"));
                			}
                		}else {
                			if (nodeName.equals((String)m.get("name"))){
                				link.put("node1",m.get("id"));
                			}
                			if (nodeName2.equals((String)m.get("name"))){
                				link.put("node2",m.get("id"));
                			}
                		}
                		link.put("name","¥"+df.getAccountMoney());
                	}
                	if(!link.keySet().contains("node1")||!link.keySet().contains("node2")) continue;
                	links.add(link);
                }
            }
            /** 封装交易主体交易对手line end**/
            
            hashMap.put("links",links);
            hashMap.put("nodes",nodes);
        }

		return hashMap;
	}
	
	public Map<String, Object> moneyFlowPersonalZzjg(DealFlowVo vo){
		Map<String, Object> result = new HashMap<String,Object>();
		List<DealFlow> list = this.zjlxfx(vo);
		if(list!=null && list.size()>0) {
			if(vo.getSign()!=null && vo.getSign()==1) {
				List<String> nodeNameList = new ArrayList<String>();
				String rootNodeName = vo.getCheckedAccount();
				String str = vo.getCheckedAccount().split(",")[0];
				if(StringUtils.isNotEmpty(str)) {
					String accountBody = str.split("_")[0];
					if(StringUtils.isNotEmpty(accountBody)) {
						rootNodeName=accountBody;
					}else {
						rootNodeName = str.split("_")[1];
					}
				}
				nodeNameList.add(rootNodeName);
				//流出资金分析，要先看看有没有对手信息
				Map<String, Object> data1 = new HashMap<String,Object>();
				List<Nnode> rootList1 = new ArrayList<Nnode>();
				Nnode rootNode1 = new Nnode();
				rootNode1.setName(rootNodeName);
				rootNode1.setSymbol("rectangle");
				rootNode1.setChildren(new ArrayList<>());
				rootList1.add(rootNode1);
				if(StringUtils.isNotEmpty(vo.getDetailKeyword())) {
					//如果有对手信息，那么先把对手信息封装进上半部分
					int count=0;
					BigDecimal totalMoney = new BigDecimal(0);
					for(DealFlow df:list) {
						if((df.getAccountBody()!=null && df.getAccountBody().equals(vo.getDetailKeyword()))
								|| (df.getAccountBodyCard()!=null && df.getAccountBodyCard().equals(vo.getDetailKeyword()))) {
							count++;
							totalMoney = totalMoney.add(df.getAccountMoney());
						}
					}
					if(count>0) {
						List<Nnode> lineList = new ArrayList<Nnode>();
						Nnode lineNode = new Nnode();
						lineNode.setName("共"+count+"次,金额"+totalMoney+"元");
						lineNode.setSymbol("arrowdown");
						lineList.add(lineNode);
						rootNode1.setChildren(lineList);
						
						List<Nnode> childList = new ArrayList<Nnode>();
						Nnode childNode = new Nnode();
						childNode.setName(vo.getDetailKeyword());
						childNode.setSymbol("rectangle");
						childNode.setLevel(1);
						childList.add(childNode);
						lineNode.setChildren(childList);
						
						nodeNameList.add(vo.getDetailKeyword());
					}
				}
				data1.put("children", rootList1);
				result.put("data1", data1);
				
				Map<String, Object> data2 = new HashMap<String,Object>();
				List<Nnode> rootList = new ArrayList<Nnode>();
				Nnode rootNode = new Nnode();
				rootNode.setName(rootNodeName);
				rootNode.setSymbol("rectangle");
				rootList.add(rootNode);
				
				int level=0;
				boolean flag=true;
				List<List<Nnode>> nodeList = new ArrayList<List<Nnode>>();
				nodeList.add(rootList);
				while(flag) {
					int Nlevel = level+1;
					List<Nnode> nextNodeList = new ArrayList<Nnode>();
					for(Nnode node : nodeList.get(level)) {
						List<Nnode> lineList = new ArrayList<Nnode>();
						Map<String, Llink> links = getLlink(node.getName(), list,nodeNameList);
						for(String key:links.keySet()) {
							Nnode lineNode = new Nnode();
							lineNode.setName("共"+links.get(key).getCount()+"次,金额"+links.get(key).getTotalMoney()+"元");
							lineNode.setSymbol("arrowdown");
							lineList.add(lineNode);
							
							List<Nnode> childList = new ArrayList<Nnode>();
							Nnode childNode = new Nnode();
							childNode.setName(key);
							childNode.setSymbol("rectangle");
							childNode.setDown(true);
							childNode.setLevel(Nlevel);
							childList.add(childNode);
							lineNode.setChildren(childList);
							
							nextNodeList.add(childNode);
						}
						node.setChildren(lineList);
					}
					nodeList.add(nextNodeList);
					level++;
					if(nextNodeList.size()<=0) {
						flag=false;
					}
				}
				
				data2.put("children", rootList);
				result.put("data2", data2);
			}else {
				//查询自己来源，这里的树要倒过来画
				List<String> nodeNameList = new ArrayList<String>();
				String rootNodeName = vo.getCheckedAccount();
				String str = vo.getCheckedAccount().split(",")[0];
				if(StringUtils.isNotEmpty(str)) {
					String accountBody = str.split("_")[0];
					if(StringUtils.isNotEmpty(accountBody)) {
						rootNodeName=accountBody;
					}else {
						rootNodeName = str.split("_")[1];
					}
				}
				nodeNameList.add(rootNodeName);
				
				Map<String, Object> data1 = new HashMap<String,Object>();
				List<Nnode> rootList = new ArrayList<Nnode>();
				Nnode rootNode = new Nnode();
				rootNode.setName(rootNodeName);
				rootNode.setSymbol("rectangle");
				rootList.add(rootNode);
				
				int level=0;
				boolean flag=true;
				List<List<Nnode>> nodeList = new ArrayList<List<Nnode>>();
				nodeList.add(rootList);
				while(flag) {
					int Nlevel = level+1;
					List<Nnode> nextNodeList = new ArrayList<Nnode>();
					for(Nnode node : nodeList.get(level)) {
						List<Nnode> lineList = new ArrayList<Nnode>();
						Map<String, Llink> links = getLlink(node.getName(), list,nodeNameList);
						for(String key:links.keySet()) {
							Nnode lineNode = new Nnode();
							lineNode.setName("共"+links.get(key).getCount()+"次,金额"+links.get(key).getTotalMoney()+"元");
							lineNode.setSymbol("arrowdown");
							lineList.add(lineNode);
							
							List<Nnode> childList = new ArrayList<Nnode>();
							Nnode childNode = new Nnode();
							childNode.setName(key);
							childNode.setSymbol("rectangle");
							childNode.setDown(true);
							childNode.setLevel(Nlevel);
							childList.add(childNode);
							lineNode.setChildren(childList);
							
							nextNodeList.add(childNode);
						}
						node.setChildren(lineList);
					}
					nodeList.add(nextNodeList);
					level++;
					if(nextNodeList.size()<=0) {
						flag=false;
					}
				}
				
				data1.put("children", rootList);
				result.put("data1", data1);
				
				Map<String, Object> data2 = new HashMap<String,Object>();
				List<Nnode> rootList2 = new ArrayList<Nnode>();
				Nnode rootNode2 = new Nnode();
				rootNode2.setName(rootNodeName);
				rootNode2.setSymbol("rectangle");
				rootNode2.setChildren(new ArrayList<>());
				rootList2.add(rootNode2);
				data2.put("children", rootList2);
				result.put("data2", data2);
			}
		}else {
			result.put("data1", null);
			result.put("data2", null);
		}
		return result;
	}
	
	private Map<String, Llink> getLlink(String name,List<DealFlow> list,List<String> nodeNameList){
		Map<String, Llink> result = new HashMap<String,Llink>();
		List<String> nameList = new ArrayList<String>();
		for(DealFlow df:list) {
			String bodyName = (StringUtils.isNotEmpty(df.getAccountBody()))?df.getAccountBody():df.getAccountBodyCard();
			String rivalName = (StringUtils.isNotEmpty(df.getAccountRival()))?df.getAccountRival():df.getAccountRivalCard();
			if(bodyName.equals(name) && !nodeNameList.contains(rivalName)) {
				if(result.get(rivalName)!=null) {
					Llink l = result.get(rivalName);
					l.setCount(l.getCount()+1);
					l.setTotalMoney(l.getTotalMoney().add(df.getAccountMoney()));
				}else {
					Llink l = new Llink();
					l.setCount(l.getCount()+1);
					l.setTotalMoney(l.getTotalMoney().add(df.getAccountMoney()));
					result.put(rivalName, l);
				}
				nameList.add(rivalName);
			}
		}
		nodeNameList.addAll(nameList);
		return result;
	}
	
	public List<DealFlow> zjlxfx(DealFlowVo vo){
		String whichTable = caseManageService.getWhichTable(vo.getCasemanageId());
		Integer levels = vo.getLevels();//向下查询层数
		if(levels==null || levels<=0) levels=3;
		if(levels>10) levels=10;
		Integer days = vo.getDays();
		if(days==null || days<=0) days=3;//每笔交易间隔天数
		
		List<DealFlow> allList = new ArrayList<DealFlow>();//所有符合条件的交易笔数
		Map<String, List<Timestamp>> tempList = new HashMap<String, List<Timestamp>>();//下次循环需要迭代的数据
		int i=0;
		while(i<levels) {//递归查询每一层结果，并把结果赋值给最终的集合内
			if(i==0) {
				StringBuffer sb = new StringBuffer();
				if(vo.getSign()!=null && vo.getSign()==1 && StringUtils.isNotEmpty(vo.getDetailKeyword())) {
					sb.append("select  account_body as account_rival,account_body_card as account_rival_card,trade_date,");
					sb.append( "account_rival as account_body,account_rival_card as account_body_card,(case when sign=0 then 1 else 0 end) as sign ,account_money");
					sb.append("  from "+whichTable+" where casemanage_id='"+vo.getCasemanageId()+"' and account_rival_card is not null ");
					sb.append(" and (account_rival='"+vo.getDetailKeyword()+"' or account_rival_card ='"+vo.getDetailKeyword()+"') ");
					sb.append(" and sign='0' ");
				}else {
					sb.append("select * from "+whichTable+" where casemanage_id='"+vo.getCasemanageId()+"' and account_rival_card is not null ");
					if(vo.getSign()!=null && vo.getSign()!=-1) {
						sb.append(" AND sign= '"+vo.getSign() +"' ");
					}
				}
				sb.append(" and (1=0 ");
				if(StringUtils.isNotEmpty(vo.getCheckedAccount())){
					if(!"-1".equals(vo.getCheckedAccount())) {
						if (vo.getCheckedAccount().split(",").length<1000){
							String cards = "";
							for(String str:vo.getCheckedAccount().split(",")) {
								if(StringUtils.isNotEmpty(str)) {
									String accountBodyCard = str.split("_")[1];
									if(StringUtils.isNotEmpty(accountBodyCard)) {
										cards = cards+"'"+accountBodyCard+"',";
									}
								}
							}
							if(cards.endsWith(",")) {
								cards = cards.substring(0,cards.length()-1);
							}
							if(cards.length()>0) {
								sb.append(" or account_body_card in ("+cards+")");
							}
						}
					}
				}
				sb.append(" ) ");
				
				if(StringUtils.isNotEmpty(vo.getMinMoney())){
					sb.append(" AND account_money >="+vo.getMinMoney());
				}
				if(StringUtils.isNotEmpty(vo.getMaxMoney())){
					sb.append(" AND account_money <="+vo.getMaxMoney());
				}
				if (StringUtils.isNotEmpty(vo.getAccountRival())){
					sb.append(" AND account_rival like '%"+vo.getAccountRival()+"%'");
				}
				if (StringUtils.isNotEmpty(vo.getStarTime())){
					sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') >= '"+vo.getStarTime()+"'");
				}
				if (StringUtils.isNotEmpty(vo.getEndTime())){
					sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd hh24:mi:ss') <= '"+vo.getEndTime()+" 23:59:59'");
				}
				Query query = this.entityManager.createNativeQuery(sb.toString());
				// 将查询结果集转为Map
				query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(DealFlow.class));
				List<DealFlow> list = query.getResultList();
				allList.addAll(list);
				tempList = transList(list);
			}else {
				List<DealFlow> list = new ArrayList<DealFlow>();
				for(String name:tempList.keySet()) {
					StringBuffer sb = new StringBuffer();
					sb.append("select * from "+whichTable+" where casemanage_id='"+vo.getCasemanageId()+"' and account_rival_card is not null ");
					sb.append(" AND account_body_card='"+name+"' ");
					if(StringUtils.isNotEmpty(vo.getMinMoney())){
						sb.append(" AND account_money >="+vo.getMinMoney());
					}
					if(vo.getSign()!=null && vo.getSign()!=-1) {
						sb.append("AND sign= '"+vo.getSign() +"' ");
					}
					if(StringUtils.isNotEmpty(vo.getMaxMoney())){
						sb.append(" AND account_money <="+vo.getMaxMoney());
					}
					if (StringUtils.isNotEmpty(vo.getAccountRival())){
						sb.append(" AND account_rival like '%"+vo.getAccountRival()+"%'");
					}
					appendTime(sb, tempList.get(name), vo.getSign(), vo.getStarTime(), vo.getEndTime(), days);
					Query query = this.entityManager.createNativeQuery(sb.toString());
					// 将查询结果集转为Map
					query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(DealFlow.class));
					List<DealFlow> l = query.getResultList();
					list.addAll(l);
					
					//还要查询交易对手中有没有是该账号的
					StringBuffer sb2 = new StringBuffer();
					sb2.append("select account_body as account_rival,account_body_card as account_rival_card,trade_date,"
							+ "account_rival as account_body,account_rival_card as account_body_card,(case when sign=0 then 1 else 0 end) as sign ,account_money"
							+ " from "+whichTable+" where casemanage_id='"+vo.getCasemanageId()+"' and account_rival_card is not null ");
					sb2.append(" AND account_rival_card='"+name+"' ");
					if(StringUtils.isNotEmpty(vo.getMinMoney())){
						sb2.append(" AND account_money >="+vo.getMinMoney());
					}
					if(vo.getSign()!=null && vo.getSign()!=-1) {
						if(vo.getSign()==1) sb2.append("AND sign= '0' ");
						if(vo.getSign()==0) sb2.append("AND sign= '1' ");
					}
					if(StringUtils.isNotEmpty(vo.getMaxMoney())){
						sb2.append(" AND account_money <="+vo.getMaxMoney());
					}
					if (StringUtils.isNotEmpty(vo.getAccountRival())){
						sb2.append(" AND account_rival like '%"+vo.getAccountRival()+"%'");
					}
					appendTime(sb2, tempList.get(name), vo.getSign(), vo.getStarTime(), vo.getEndTime(), days);
					Query query2 = this.entityManager.createNativeQuery(sb2.toString());
					// 将查询结果集转为Map
					query2.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(DealFlow.class));
					List<DealFlow> l2 = query2.getResultList();
					list.addAll(l2);
				}
				allList.addAll(list);
				tempList = transList(list);
			}
			i++;//自动进入下一层的查询中
		}
		
		List<DealFlow> resultList = new ArrayList<DealFlow>();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		for(int m=0;m<allList.size();m++) {
			DealFlow df = allList.get(m);
			String accountBody = df.getAccountBody()==null?"":df.getAccountBody();
			String accountBodyCard = df.getAccountBodyCard()==null?"":df.getAccountBodyCard();
			String accountRival = df.getAccountRival()==null?"":df.getAccountRival();
			String accountRivalCard = df.getAccountRivalCard()==null?"":df.getAccountRivalCard();
			String tradeDate = sdf.format(df.getTradeDate());
			BigDecimal accountMoney = df.getAccountMoney();
			if(!"00:00:00".equals(tradeDate.substring(11))) {//没有时分秒的交易日期不虑重
				boolean flag=false;
				for(int n=0;n<m;n++) {
					DealFlow df2 = allList.get(n);
					String accountBody2 = df2.getAccountBody()==null?"":df2.getAccountBody();
					String accountBodyCard2 = df2.getAccountBodyCard()==null?"":df2.getAccountBodyCard();
					String accountRival2 = df2.getAccountRival()==null?"":df2.getAccountRival();
					String accountRivalCard2 = df2.getAccountRivalCard()==null?"":df2.getAccountRivalCard();
					String tradeDate2 = sdf.format(df2.getTradeDate());
					BigDecimal accountMoney2 = df2.getAccountMoney();
					
					if(accountBody.equals(accountBody2) 
							&& accountBodyCard.equals(accountBodyCard2)
							&& accountRival.equals(accountRival2)
							&& accountRivalCard.equals(accountRivalCard2)
							&& tradeDate.equals(tradeDate2)
							&& accountMoney.equals(accountMoney2)) {
						//什么也不处理，继续循环
						flag=true;
						break;
					}
				}
				if(!flag) {
					resultList.add(df);
				}
			}else {
				resultList.add(df);
			}
		}
		return resultList;
	}
	
	public List<Map<String, Object>> getRivals(DealFlowVo vo){
		String whichTable = caseManageService.getWhichTable(vo.getCasemanageId());
		StringBuffer sb = new StringBuffer();
		sb.append("select distinct (case when account_rival is not null then account_rival else account_rival_card end) as value,(case when account_rival is not null then account_rival else account_rival_card end) as label  from "+whichTable+" where casemanage_id='"+vo.getCasemanageId()+"' and account_rival_card is not null ");
		sb.append(" and (1=0 ");
		if(StringUtils.isNotEmpty(vo.getCheckedAccount())){
			if(!"-1".equals(vo.getCheckedAccount())) {
				if (vo.getCheckedAccount().split(",").length<1000){
					String cards = "";
					for(String str:vo.getCheckedAccount().split(",")) {
						if(StringUtils.isNotEmpty(str)) {
							String accountBodyCard = str.split("_")[1];
							if(StringUtils.isNotEmpty(accountBodyCard)) {
								cards = cards+"'"+accountBodyCard+"',";
							}
						}
					}
					if(cards.endsWith(",")) {
						cards = cards.substring(0,cards.length()-1);
					}
					if(cards.length()>0) {
						sb.append(" or account_body_card in ("+cards+")");
					}
				}
			}
		}
		sb.append(" ) ");
		return jdbcTemplate.queryForList(sb.toString());
	}
	
	private void appendTime(StringBuffer sb,List<Timestamp> l,Integer sign,String starTime,String endTime,int days) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		if(l!=null && l.size()>0) {
			if(sign==1) {//支出流向，查询后三天数据
				sb.append(" and (1=0 ");
				for(Timestamp ts :l) {
					sb.append(" or (to_char(TRADE_DATE,'yyyy-mm-dd hh24:mi:ss') > '"+sdf.format(ts)+"'");
					if(StringUtils.isNotEmpty(endTime)) {
						try {
							Long maxTime = (ts.getTime() + (1000*24*days*60*60));
							Long eTime = sdf.parse(endTime+" 23:59:59").getTime();
							if(eTime<maxTime) {
								sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd hh24:mi:ss') <= '"+endTime+" 23:59:59'");
							}else {
								sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd hh24:mi:ss') <= '"+sdf.format(new Date(maxTime))+"'");
							}
						} catch (ParseException e) {
							e.printStackTrace();
							sb.append(" and 1=0 ");
						}
					}else {
						String maxTime = sdf.format(new Date(ts.getTime() + (1000*24*days*60*60)));
						sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd hh24:mi:ss') <= '"+maxTime+"'");
					}
					sb.append(" ) ");
				}
				sb.append(" ) ");
			}else {//收入流向，查询前三天数据
				sb.append(" and (1=0 ");
				for(Timestamp ts :l) {
					sb.append(" or (to_char(TRADE_DATE,'yyyy-mm-dd hh24:mi:ss') <= '"+sdf.format(ts)+"'");
					if(StringUtils.isNotEmpty(starTime)) {
						try {
							Long minTime = (ts.getTime() - (1000*24*days*60*60));
							Long sTime = sdf.parse(starTime+" 23:59:59").getTime();
							if(sTime<minTime) {
								sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd hh24:mi:ss') > '"+sdf.format(new Date(minTime))+"'");
							}else {
								sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') > '"+starTime+"'");
							}
						} catch (ParseException e) {
							e.printStackTrace();
							sb.append(" and 1=0 ");
						}
					}else {
						String maxTime = sdf.format(new Date(ts.getTime() - (1000*24*days*60*60)));
						sb.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd hh24:mi:ss') > '"+maxTime+"'");
					}
					sb.append(" ) ");
				}
				sb.append(" ) ");
			}
		} else {
			sb.append(" and 1=0 ");
		}
	}
	
	/**
	 * 对所有的结果集进行分类，按照对手户名/卡号进行最小交易时间和最大交易时间的整理
	 * @param list
	 * @return
	 */
	private Map<String, List<Timestamp>> transList(List<DealFlow> list){
		Map<String, List<Timestamp>> result = new HashMap<String,List<Timestamp>>();
		for(DealFlow df:list) {
			if(df.getTradeDate()!=null) {
//			String name = StringUtils.isNotEmpty(df.getAccountRival())?df.getAccountRival():df.getAccountRivalCard();
				String name = df.getAccountRivalCard();//再三考虑，宁肯少展示点数据，也不敢让数据出现错误，所以这里显示的是卡号
				if(result.get(name)==null) {
					List<Timestamp> l = new ArrayList<Timestamp>();
					l.add(df.getTradeDate());
					result.put(name, l);
				}else {
					List<Timestamp> l = result.get(name);
					if(!l.contains(df.getTradeDate())) {
						l.add(df.getTradeDate());
					}
				}
			}
		}
		return result;
	}
	
	class Nnode{
		private String name;
		private String symbol;
		private List<Nnode> children;
		private Integer Level;
		private boolean down=false;
		public String getName() {
			return name;
		}
		public void setName(String name) {
			this.name = name;
		}
		public String getSymbol() {
			return symbol;
		}
		public void setSymbol(String symbol) {
			this.symbol = symbol;
		}
		public List<Nnode> getChildren() {
			return children;
		}
		public void setChildren(List<Nnode> children) {
			this.children = children;
		}
		public Integer getLevel() {
			return Level;
		}
		public void setLevel(Integer level) {
			Level = level;
		}
		public boolean isDown() {
			return down;
		}
		public void setDown(boolean down) {
			this.down = down;
		}
	}
	
	class Llink{
		private int count=0;
		private BigDecimal totalMoney=new BigDecimal(0);
		public int getCount() {
			return count;
		}
		public void setCount(int count) {
			this.count = count;
		}
		public BigDecimal getTotalMoney() {
			return totalMoney;
		}
		public void setTotalMoney(BigDecimal totalMoney) {
			this.totalMoney = totalMoney;
		}
	}
}
